SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROC [dbo].[SP_TXMVTO_CNTB_Q06]    
/*-------------------------------------------------------------------*/    
/*----- Empresa           : OFISIS S.A.                          ----*/    
/*----- Cliente           : OFISIS S.A.                          ----*/    
/*----- Sistema           : Contabilidad                         ----*/    
/*----- Modulo            : Empresa                              ----*/    
/*----- Programa          : Estado de Cambios Patrimonio Neto    ----*/    
/*----- Nombre            : SP_TXMVTO_CNTB_Q06                   ----*/    
/*----- Script            : TXMVTO_CNTB                          ----*/    
/*----- Desarrollado por  : Marco Acuna                ----*/    
/*----- Fecha             : 27/12/2002                           ----*/    
/*----- Base de Datos     : MS SQL Server                        ----*/    
/*----- Version BD        : 7.0                                  ----*/    
/*-------------------------------------------------------------------*/    
/*---- Modificado 1 por     :                                    ----*/  
/*---- Fecha Modificaci>n   :                                    ----*/  
/*---- Detalle Modificaci>n :                                    ----*/  
/*-------------------------------------------------------------------*/    
/*----- Drop proc SP_TXMVTO_CNTB_Q06                             ----*/    
/*----- Grant all on SP_TXMVTO_CNTB_Q06 to PUBLIC                ----*/    
/*----- SP_TXMVTO_CNTB_Q06 '01',1,2008,8                        ----*/    
/*-------------------------------------------------------------------*/    
    
@ISCO_EMPR  TD_VC_002,    
@INNU_CNTB_EMPR TD_IN_001,    
@INNU_ANNO TD_IN_002_MESE,     
@INNU_MESE TD_IN_004_ANNO    
    
As    
Create Table #TWMVTO_CNTB_Q06_1    
( NU_ANNO       Integer null ,    
  CO_TITU       varchar(03) null ,    
  NO_TITU varchar(80) null ,    
  CO_COLU       varchar(03) null ,         
  NO_COLU varchar(80) null ,      
  IM_SALD_0001 Numeric(16,4) NULL,      
  IM_SALD_0002 Numeric(16,4) NULL ,      
  IM_SALD_0003 Numeric(16,4) NULL,      
  IM_SALD_0004 Numeric(16,4) NULL,      
  IM_SALD_0005 Numeric(16,4) NULL,  
  IM_SALD_0006 Numeric(16,4) NULL,  
  IM_SALD_0007 Numeric(16,4) NULL)    
    
Create Table #TWMVTO_CNTB_Q06_2    
( NU_ANNO       Integer  null,    
  CO_TITU       varchar(03)null  ,    
  NO_TITU varchar(80) null ,    
  CO_COLU       varchar(03) null ,         
  NO_COLU varchar(80) null ,      
  IM_SALD_0001 Numeric(16,4) NULL,      
  IM_SALD_0002 Numeric(16,4) NULL ,      
  IM_SALD_0003 Numeric(16,4) NULL,      
  IM_SALD_0004 Numeric(16,4) NULL,      
  IM_SALD_0005 Numeric(16,4) NULL,  
  IM_SALD_0006 Numeric(16,4) NULL,  
  IM_SALD_0007 Numeric(16,4) NULL)    
    
Declare    
@CSCO_TITU  TD_VC_003,    
@CSNO_TITU  TD_VC_050,    
@CSCO_COLU  TD_VC_003,    
@CSNO_COLU  TD_VC_050 ,    
@CNNU_ORDE  TD_IN_001,    
@CNIM_SALD  Numeric(16,4),    
@VNNV_QUIE  Integer    
    
select @VNNV_QUIE= convert(integer, substring(nv_quie_cnta,1,2))    
from tmpara_cont    
where co_empr = @ISCO_EMPR    
      
Declare CU_TXMVTO_CNTB_Q06_1 Cursor    
For    
    
Select MAX(T1.CO_TITU), MAX(T1.NO_TITU),MAX(T2.CO_COLU), MAX(T2.NO_COLU), MAX(T2.NU_ORDE),     
ISNULL(SUM(T3.IM_CARG_CNTB - T3.IM_ABON_CNTB),0)    
FROM TMPATR_NETO T1     
  Left Outer Join TASALD_EMPR T3    
  On T3.NU_CNTB_EMPR = @INNU_CNTB_EMPR    
  AND T3.NU_ANNO = @INNU_ANNO    
  AND T3.NU_MESE = 0     
  AND T1.CO_EMPR = T3.CO_EMPR    
  AND T1.CO_CNTA_EMPR = T3.CO_CNTA_EMPR    
  AND T3.TI_AUXI_EMPR IS NULL    
  AND T3.CO_AUXI_EMPR IS NULL    
  AND T3.CO_ORDE_SERV IS NULL, TMPATR_NETO T2     
WHERE T1.CO_EMPR = @ISCO_EMPR    
AND T1.ST_TIPO = 'T'    
AND T2.CO_EMPR = T1.CO_EMPR    
AND T1.CO_CNTA_EMPR LIKE T2.CO_CNTA_EMPR + '%'
AND T2.ST_TIPO = 'C'    
GROUP BY  T2.CO_CNTA_EMPR
    
OPEN CU_TXMVTO_CNTB_Q06_1    
FETCH CU_TXMVTO_CNTB_Q06_1 INTO @CSCO_TITU, @CSNO_TITU, @CSCO_COLU , @CSNO_COLU, @CNNU_ORDE, @CNIM_SALD    
 While @@fetch_status = 0    
 BEGIN    
   IF @CNNU_ORDE = 1    
   INSERT INTO #TWMVTO_CNTB_Q06_1(NU_ANNO,CO_TITU,NO_TITU,CO_COLU,NO_COLU,IM_SALD_0001,IM_SALD_0002,  
   IM_SALD_0003,IM_SALD_0004,IM_SALD_0005,IM_SALD_0006,IM_SALD_0007)   
   VALUES(@INNU_ANNO-1,'', '', '', '',@CNIM_SALD,0.00,0.00,0.00,0.00,0.00,0.00)    
    
   IF @CNNU_ORDE = 2    
   INSERT INTO #TWMVTO_CNTB_Q06_1(NU_ANNO,CO_TITU,NO_TITU,CO_COLU,NO_COLU,IM_SALD_0001,IM_SALD_0002,IM_SALD_0003,      
   IM_SALD_0004,IM_SALD_0005,IM_SALD_0006,IM_SALD_0007)   
   VALUES(@INNU_ANNO-1,'','','', '',0.00,@CNIM_SALD,0.00,0.00,0.00,0.00,0.00)    
    
   IF @CNNU_ORDE = 3    
   INSERT INTO #TWMVTO_CNTB_Q06_1(NU_ANNO,CO_TITU,NO_TITU,CO_COLU,NO_COLU,IM_SALD_0001,IM_SALD_0002,IM_SALD_0003,      
   IM_SALD_0004,IM_SALD_0005,IM_SALD_0006,IM_SALD_0007)   
   VALUES(@INNU_ANNO-1,'','','','',0.00,0.00,@CNIM_SALD,0.00,0.00,0.00,0.00)    
      
   IF @CNNU_ORDE = 4    
   INSERT INTO #TWMVTO_CNTB_Q06_1(NU_ANNO,CO_TITU,NO_TITU,CO_COLU,NO_COLU,IM_SALD_0001,IM_SALD_0002,IM_SALD_0003,      
   IM_SALD_0004,IM_SALD_0005,IM_SALD_0006,IM_SALD_0007)   
   VALUES(@INNU_ANNO-1,'','', '', '',0.00,0.00,0.00,@CNIM_SALD,0.00,0.00,0.00)    
    
   IF @CNNU_ORDE = 5    
   INSERT INTO #TWMVTO_CNTB_Q06_1(NU_ANNO,CO_TITU,NO_TITU,CO_COLU,NO_COLU,IM_SALD_0001,IM_SALD_0002,IM_SALD_0003,      
   IM_SALD_0004,IM_SALD_0005,IM_SALD_0006,IM_SALD_0007)   
   VALUES(@INNU_ANNO-1,'','','', '',0.00,0.00,0.00,0.00,@CNIM_SALD,0.00,0.00)    
  
   IF @CNNU_ORDE = 6    
   INSERT INTO #TWMVTO_CNTB_Q06_1(NU_ANNO,CO_TITU,NO_TITU,CO_COLU,NO_COLU,IM_SALD_0001,IM_SALD_0002,IM_SALD_0003,      
   IM_SALD_0004,IM_SALD_0005,IM_SALD_0006,IM_SALD_0007)   
   VALUES(@INNU_ANNO-1,'','','', '',0.00,0.00,0.00,0.00,0.00,@CNIM_SALD,0.00)  
  
   IF @CNNU_ORDE = 7    
   INSERT INTO #TWMVTO_CNTB_Q06_1(NU_ANNO,CO_TITU,NO_TITU,CO_COLU,NO_COLU,IM_SALD_0001,IM_SALD_0002,IM_SALD_0003,      
   IM_SALD_0004,IM_SALD_0005,IM_SALD_0006,IM_SALD_0007)   
   VALUES(@INNU_ANNO-1,'','','', '',0.00,0.00,0.00,0.00,0.00,0.00,@CNIM_SALD)  
    
  FETCH CU_TXMVTO_CNTB_Q06_1 INTO @CSCO_TITU, @CSNO_TITU, @CSCO_COLU , @CSNO_COLU, @CNNU_ORDE, @CNIM_SALD     
    
 END --END DEL WHILE    
   
  
 INSERT INTO #TWMVTO_CNTB_Q06_2(NU_ANNO,CO_TITU,NO_TITU,CO_COLU,NO_COLU,IM_SALD_0001,IM_SALD_0002,IM_SALD_0003,      
 IM_SALD_0004,IM_SALD_0005,IM_SALD_0006,IM_SALD_0007)    
 Select @INNU_ANNO-1,max(CO_TITU), MAX(NO_TITU),MAX(CO_COLU), MAX(NO_COLU), SUM(IM_SALD_0001),SUM(IM_SALD_0002),SUM(IM_SALD_0003),    
 SUM(IM_SALD_0004),SUM(IM_SALD_0005),SUM(IM_SALD_0006),SUM(IM_SALD_0007)    
 From  #TWMVTO_CNTB_Q06_1    
    
Close CU_TXMVTO_CNTB_Q06_1    
Deallocate CU_TXMVTO_CNTB_Q06_1    
    
 Delete from #TWMVTO_CNTB_Q06_1    
    
/*------------------------  Segundo A?o --------------------------*/    
Declare CU_TXMVTO_CNTB_Q06_2 Cursor    
for    
    
Select T1.CO_TITU, MAX(T1.NO_TITU),MAX(T2.CO_COLU), MAX(T2.NO_COLU), MAX(T2.NU_ORDE),     
ISNULL(SUM(T3.IM_CARG_CNTB - T3.IM_ABON_CNTB),0)    
FROM TMPATR_NETO T1     
  Left Outer Join TASALD_EMPR T3     
  On T3.NU_CNTB_EMPR = @INNU_CNTB_EMPR    
  AND T3.NU_ANNO = @INNU_ANNO    
  AND T3.NU_MESE > 0     
  AND T3.NU_MESE <= @INNU_MESE    
  AND T1.CO_EMPR = T3.CO_EMPR    
  AND T1.CO_CNTA_EMPR = T3.CO_CNTA_EMPR    
  AND T3.TI_AUXI_EMPR IS NULL    
  AND T3.CO_AUXI_EMPR IS NULL    
  AND T3.CO_ORDE_SERV IS NULL,     
  TMPATR_NETO T2     
WHERE T1.CO_EMPR = @ISCO_EMPR    
AND T1.ST_TIPO = 'T'    
AND T1.CO_EMPR = T2.CO_EMPR    
AND T1.CO_CNTA_EMPR LIKE T2.CO_CNTA_EMPR + '%'
AND T2.ST_TIPO = 'C'    
GROUP BY T1.CO_TITU , T2.CO_CNTA_EMPR
     
OPEN CU_TXMVTO_CNTB_Q06_2    
FETCH CU_TXMVTO_CNTB_Q06_2 INTO @CSCO_TITU, @CSNO_TITU, @CSCO_COLU , @CSNO_COLU, @CNNU_ORDE, @CNIM_SALD    
While @@Fetch_status = 0    
 Begin     
   IF @CNNU_ORDE = 1    
   INSERT INTO #TWMVTO_CNTB_Q06_1(NU_ANNO,CO_TITU,NO_TITU,CO_COLU,NO_COLU,IM_SALD_0001,IM_SALD_0002,IM_SALD_0003,      
   IM_SALD_0004,IM_SALD_0005,IM_SALD_0006,IM_SALD_0007)   
   VALUES(@INNU_ANNO,@CSCO_TITU, @CSNO_TITU, @CSCO_COLU, @CSNO_COLU,@CNIM_SALD,0.00,0.00,0.00,0.00,0.00,0.00)    
    
   IF @CNNU_ORDE = 2    
   INSERT INTO #TWMVTO_CNTB_Q06_1(NU_ANNO,CO_TITU,NO_TITU,CO_COLU,NO_COLU,IM_SALD_0001,IM_SALD_0002,IM_SALD_0003,      
   IM_SALD_0004,IM_SALD_0005,IM_SALD_0006,IM_SALD_0007)   
   VALUES(@INNU_ANNO,@CSCO_TITU, @CSNO_TITU, @CSCO_COLU, @CSNO_COLU,0.00,@CNIM_SALD,0.00,0.00,0.00,0.00,0.00)    
    
   IF @CNNU_ORDE = 3    
   INSERT INTO #TWMVTO_CNTB_Q06_1(NU_ANNO,CO_TITU,NO_TITU,CO_COLU,NO_COLU,IM_SALD_0001,IM_SALD_0002,IM_SALD_0003,      
   IM_SALD_0004,IM_SALD_0005,IM_SALD_0006,IM_SALD_0007)   
   VALUES(@INNU_ANNO,@CSCO_TITU, @CSNO_TITU, @CSCO_COLU, @CSNO_COLU,0.00,0.00,@CNIM_SALD,0.00,0.00,0.00,0.00)    
      
   IF @CNNU_ORDE = 4    
   INSERT INTO #TWMVTO_CNTB_Q06_1(NU_ANNO,CO_TITU,NO_TITU,CO_COLU,NO_COLU,IM_SALD_0001,IM_SALD_0002,IM_SALD_0003,      
   IM_SALD_0004,IM_SALD_0005,IM_SALD_0006,IM_SALD_0007)   
   VALUES(@INNU_ANNO,@CSCO_TITU, @CSNO_TITU, @CSCO_COLU, @CSNO_COLU,0.00,0.00,0.00,@CNIM_SALD,0.00,0.00,0.00)    
    
   IF @CNNU_ORDE = 5    
   INSERT INTO #TWMVTO_CNTB_Q06_1(NU_ANNO,CO_TITU,NO_TITU,CO_COLU,NO_COLU,IM_SALD_0001,IM_SALD_0002,IM_SALD_0003,      
   IM_SALD_0004,IM_SALD_0005,IM_SALD_0006,IM_SALD_0007)   
   VALUES(@INNU_ANNO,@CSCO_TITU, @CSNO_TITU, @CSCO_COLU, @CSNO_COLU,0.00,0.00,0.00,0.00,@CNIM_SALD,0.00,0.00)    
  
   IF @CNNU_ORDE = 6    
   INSERT INTO #TWMVTO_CNTB_Q06_1(NU_ANNO,CO_TITU,NO_TITU,CO_COLU,NO_COLU,IM_SALD_0001,IM_SALD_0002,IM_SALD_0003,      
   IM_SALD_0004,IM_SALD_0005,IM_SALD_0006,IM_SALD_0007)   
   VALUES(@INNU_ANNO,@CSCO_TITU, @CSNO_TITU, @CSCO_COLU, @CSNO_COLU,0.00,0.00,0.00,0.00,0.00,@CNIM_SALD,0.00)  
    
   IF @CNNU_ORDE = 7    
   INSERT INTO #TWMVTO_CNTB_Q06_1(NU_ANNO,CO_TITU,NO_TITU,CO_COLU,NO_COLU,IM_SALD_0001,IM_SALD_0002,IM_SALD_0003,      
   IM_SALD_0004,IM_SALD_0005,IM_SALD_0006,IM_SALD_0007)   
   VALUES(@INNU_ANNO,@CSCO_TITU, @CSNO_TITU, @CSCO_COLU, @CSNO_COLU,0.00,0.00,0.00,0.00,0.00,0.00,@CNIM_SALD)  
  FETCH CU_TXMVTO_CNTB_Q06_2 INTO @CSCO_TITU, @CSNO_TITU, @CSCO_COLU , @CSNO_COLU, @CNNU_ORDE, @CNIM_SALD     
End -- END WHILE    
    
INSERT INTO #TWMVTO_CNTB_Q06_2(NU_ANNO,CO_TITU,NO_TITU,CO_COLU,NO_COLU,IM_SALD_0001,IM_SALD_0002,IM_SALD_0003,      
   IM_SALD_0004,IM_SALD_0005,IM_SALD_0006,IM_SALD_0007)    
  Select @INNU_ANNO,CO_TITU, MAX(NO_TITU),MAX(CO_COLU), MAX(NO_COLU), SUM(IM_SALD_0001),SUM(IM_SALD_0002),SUM(IM_SALD_0003),    
  SUM(IM_SALD_0004),SUM(IM_SALD_0005),SUM(IM_SALD_0006),SUM(IM_SALD_0007)    
  From  #TWMVTO_CNTB_Q06_1    
  GROUP BY CO_TITU    
    
Close CU_TXMVTO_CNTB_Q06_2    
Deallocate CU_TXMVTO_CNTB_Q06_2    
    
Select NU_ANNO , NO_TITU, IM_SALD_0001, IM_SALD_0002,IM_SALD_0003, IM_SALD_0004, IM_SALD_0005,IM_SALD_0006, IM_SALD_0007    
from  #TWMVTO_CNTB_Q06_2    
    
Return    
/*------------------------------------- Fin ------------------------------------*/    

GO
